package com.example.gymmanagebackend.mapper;

import com.example.gymmanagebackend.pojo.Evaluate;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface EvaluateMapper {

    @Options(useGeneratedKeys = true, keyProperty = "evaluate_id", keyColumn = "evaluate_id")
    @Insert("INSERT INTO Evaluate (member_name, evaluate_date, content, rating) VALUES (#{member_name}, #{evaluate_date}, #{content}, #{rating})")
    int addEvaluate(Evaluate evaluate);

    @Update("UPDATE Evaluate SET member_name = #{member_name}, evaluate_date = #{evaluate_date}, content = #{content}, rating = #{rating} WHERE evaluate_id = #{evaluate_id}")
    int updateEvaluate(Evaluate evaluate);

    @Delete("DELETE FROM Evaluate WHERE evaluate_id = #{id}")
    int deleteEvaluateById(int id);

    @Select("SELECT * FROM Evaluate")
    List<Evaluate> getAllEvaluates();

    @Select("SELECT * FROM Evaluate WHERE evaluate_id = #{id}")
    Evaluate getEvaluateById(int id);

    @Select("SELECT DATE_FORMAT(evaluate_date, '%Y-%m') AS month, COUNT(*) AS count FROM Evaluate GROUP BY DATE_FORMAT(evaluate_date, '%Y-%m')")
    List<Evaluate> getEvaluateSubmission();
    @Select("SELECT * FROM Evaluate ORDER BY evaluate_date DESC LIMIT #{offset}, #{limit}")
    List<Evaluate> getEvaluateSubmissionWithPagination(@Param("offset") int offset, @Param("limit") int limit);

    @Select("SELECT COUNT(*) FROM Evaluate")
    int countEvaluate();
    @Select("SELECT rating, COUNT(*) AS count FROM Evaluate GROUP BY rating")
    List<Map<String, Object>> getEvaluateRatingDistribution();

    @Select("SELECT rating, content, COUNT(*) AS count FROM Evaluate GROUP BY rating, content ORDER BY count DESC LIMIT 3")
    List<Map<String, Object>> getTopEvaluateContents();
}
